﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Juece.Report
{
    public partial class 月原辅料收发平衡表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double 油管光管期初库存;
        protected double 油管光管本期入库;
        protected double 油管光管本期出库;
        protected double 油管光管期末库存;
        protected double 油管光管未入库;

        protected double 套管光管期初库存;
        protected double 套管光管本期入库;
        protected double 套管光管本期出库;
        protected double 套管光管期末库存;
        protected double 套管光管未入库;

        protected double 套管N80光管期初库存;
        protected double 套管N80光管本期入库;
        protected double 套管N80光管本期出库;
        protected double 套管N80光管期末库存;
        protected double 套管N80光管未入库;

        protected double 油管接箍光管期初库存;
        protected double 油管接箍光管本期入库;
        protected double 油管接箍光管本期出库;
        protected double 油管接箍光管期末库存;
        protected double 油管接箍光管未入库;

        protected double 套管接箍光管期初库存;
        protected double 套管接箍光管本期入库;
        protected double 套管接箍光管本期出库;
        protected double 套管接箍光管期末库存;
        protected double 套管接箍光管未入库;

        protected double 成品接箍期初库存;
        protected double 成品接箍本期入库;
        protected double 成品接箍本期出库;
        protected double 成品接箍期末库存;
        protected double 成品接箍未入库;

        protected double 成品N80接箍期初库存;
        protected double 成品N80接箍本期入库;
        protected double 成品N80接箍本期出库;
        protected double 成品N80接箍期末库存;
        protected double 成品N80接箍未入库;

        protected int 自产油管接箍期初库存;
        protected int 自产油管接箍本期入库;
        protected int 自产油管接箍本期出库;
        protected int 自产油管接箍期末库存;
        protected int 自产油管接箍未入库;

        protected int 自产套管接箍期初库存;
        protected int 自产套管接箍本期入库;
        protected int 自产套管接箍本期出库;
        protected int 自产套管接箍期末库存;
        protected int 自产套管接未入库;

        protected double 聚乙烯期初库存;
        protected double 聚乙烯本期入库;
        protected double 聚乙烯本期出库;
        protected double 聚乙烯期末库存;

        protected double 钢带期初库存;
        protected double 钢带本期入库;
        protected double 钢带本期出库;
        protected double 钢带期末库存;

        protected double 套管螺纹保护环期初库存;
        protected double 套管螺纹保护环本期入库;
        protected double 套管螺纹保护环本期出库;
        protected double 套管螺纹保护环期末库存;     

        protected double 油管螺纹保护环期初库存;
        protected double 油管螺纹保护环本期入库;
        protected double 油管螺纹保护环本期出库;
        protected double 油管螺纹保护环期末库存;

        protected double 自产螺纹保护环期初库存;
        protected double 自产螺纹保护环本期入库;
        protected double 自产螺纹保护环本期出库;
        protected double 自产螺纹保护环期末库存;

        protected double 稀释剂期初库存;
        protected double 稀释剂本期入库;
        protected double 稀释剂本期出库;
        protected double 稀释剂期末库存;

        protected double 油漆期初库存;
        protected double 油漆本期入库;
        protected double 油漆本期出库;
        protected double 油漆期末库存;

        protected double 打包带期初库存;
        protected double 打包带本期入库;
        protected double 打包带本期出库;
        protected double 打包带期末库存;

        protected double 螺纹脂期初库存;
        protected double 螺纹脂本期入库;
        protected double 螺纹脂本期出库;
        protected double 螺纹脂期末库存;

        protected double 色环漆期初库存;
        protected double 色环漆本期入库;
        protected double 色环漆本期出库;
        protected double 色环漆期末库存;

        protected double 接箍漆期初库存;
        protected double 接箍漆本期入库;
        protected double 接箍漆本期出库;
        protected double 接箍漆期末库存;

        protected double 白墨期初库存;
        protected double 白墨本期入库;
        protected double 白墨本期出库;
        protected double 白墨期末库存;

        protected double 草支垫期初库存;
        protected double 草支垫本期入库;
        protected double 草支垫本期出库;
        protected double 草支垫期末库存;

        protected int year;
        protected int month;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
                this.Year0.Text = DateTime.Today.Year.ToString();
            }
           Button_Query_Click(null, null);
        }
       
        protected void Button_Query_Click(object sender, EventArgs e)
        {
            year = int.Parse(this.Year0.Text);
            month = int.Parse(this.Month0.Text);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //油管光管
            string sql = @"            
            select dbo.原料期初库存(@年度,@月份,1) as 期初库存,dbo.原料本期收入(@年度,@月份,1) as 本期收入,dbo.原料本期发出(@年度,@月份,1) as 本期发出,dbo.原料期末库存(@年度,@月份,1) as 期末库存,dbo.原料未入库(@年度,@月份,1) as 未入库";
            sql = sql.Replace("@年度", year.ToString());
            sql = sql.Replace("@月份", month.ToString());
            Cmd.CommandText = sql;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管光管期初库存 = Reader.GetDouble(0);
                油管光管本期入库 = Reader.GetDouble(1);
                油管光管本期出库 = Reader.GetDouble(2);
                油管光管期末库存 = Reader.GetDouble(3);
                油管光管未入库 = Reader.GetDouble(4);
            }
            Reader.Close();
            //套管光管
            string sql1 = @"            
            select dbo.原料期初库存(@年度,@月份,2) as 期初库存,dbo.原料本期收入(@年度,@月份,2) as 本期收入,dbo.原料本期发出(@年度,@月份,2) as 本期发出,dbo.原料期末库存(@年度,@月份,2) as 期末库存,dbo.原料未入库(@年度,@月份,2) as 未入库";
            sql1 = sql1.Replace("@年度", year.ToString());
            sql1 = sql1.Replace("@月份", month.ToString());
            Cmd.CommandText = sql1;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管光管期初库存 = Reader.GetDouble(0);
                套管光管本期入库 = Reader.GetDouble(1);
                套管光管本期出库 = Reader.GetDouble(2);
                套管光管期末库存 = Reader.GetDouble(3);
                套管光管未入库 = Reader.GetDouble(4);
            }
            Reader.Close();
            //套管N80光管
            string sqlN80 = @"            
            select dbo.原料期初库存(@年度,@月份,102) as 期初库存,dbo.原料本期收入(@年度,@月份,102) as 本期收入,dbo.原料本期发出(@年度,@月份,102) as 本期发出,dbo.原料期末库存(@年度,@月份,102) as 期末库存,dbo.原料未入库(@年度,@月份,102) as 未入库";
            sqlN80 = sqlN80.Replace("@年度", year.ToString());
            sqlN80 = sqlN80.Replace("@月份", month.ToString());
            Cmd.CommandText = sqlN80;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管N80光管期初库存 = Reader.GetDouble(0);
                套管N80光管本期入库 = Reader.GetDouble(1);
                套管N80光管本期出库 = Reader.GetDouble(2);
                套管N80光管期末库存 = Reader.GetDouble(3);
                套管N80光管未入库 = Reader.GetDouble(4);
            }
            Reader.Close();  
            //油管接箍光管
            string sql2 = @"            
            select dbo.原料期初库存(@年度,@月份,3) as 期初库存,dbo.原料本期收入(@年度,@月份,3) as 本期收入,dbo.原料本期发出(@年度,@月份,3) as 本期发出,dbo.原料期末库存(@年度,@月份,3) as 期末库存,dbo.原料未入库(@年度,@月份,3) as 未入库";
            sql2 = sql2.Replace("@年度", year.ToString());
            sql2 = sql2.Replace("@月份", month.ToString());
            Cmd.CommandText = sql2;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管接箍光管期初库存 = Reader.GetDouble(0);
                油管接箍光管本期入库 = Reader.GetDouble(1);
                油管接箍光管本期出库 = Reader.GetDouble(2);
                油管接箍光管期末库存 = Reader.GetDouble(3);
                油管接箍光管未入库 = Reader.GetDouble(4);
            }
            Reader.Close();
            //套管接箍光管
            string sql3 = @"            
            select dbo.原料期初库存(@年度,@月份,4) as 期初库存,dbo.原料本期收入(@年度,@月份,4) as 本期收入,dbo.原料本期发出(@年度,@月份,4) as 本期发出,dbo.原料期末库存(@年度,@月份,4) as 期末库存,dbo.原料未入库(@年度,@月份,4) as 未入库";
            sql3 = sql3.Replace("@年度", year.ToString());
            sql3 = sql3.Replace("@月份", month.ToString());
            Cmd.CommandText = sql3;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管接箍光管期初库存 = Reader.GetDouble(0);
                套管接箍光管本期入库 = Reader.GetDouble(1);
                套管接箍光管本期出库 = Reader.GetDouble(2);
                套管接箍光管期末库存 = Reader.GetDouble(3);
                套管接箍光管未入库 = Reader.GetDouble(4);
            }
            Reader.Close();

            //成品接箍
            string sql4 = @"            
            select dbo.原料期初库存(@年度,@月份,6) as 期初库存,dbo.原料本期收入(@年度,@月份,6) as 本期收入,dbo.原料本期发出(@年度,@月份,6) as 本期发出,dbo.原料期末库存(@年度,@月份,6) as 期末库存,dbo.原料未入库(@年度,@月份,6) as 未入库";
            sql4 = sql4.Replace("@年度", year.ToString());
            sql4 = sql4.Replace("@月份", month.ToString());
            Cmd.CommandText = sql4;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                成品接箍期初库存 = Reader.GetDouble(0);
                成品接箍本期入库 = Reader.GetDouble(1);
                成品接箍本期出库 = Reader.GetDouble(2);
                成品接箍期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();

            //成品N80接箍
            string sql20 = @"            
            select dbo.原料期初库存(@年度,@月份,106) as 期初库存,dbo.原料本期收入(@年度,@月份,106) as 本期收入,dbo.原料本期发出(@年度,@月份,106) as 本期发出,dbo.原料期末库存(@年度,@月份,106) as 期末库存,dbo.原料未入库(@年度,@月份,106) as 未入库";
            sql20 = sql20.Replace("@年度", year.ToString());
            sql20 = sql20.Replace("@月份", month.ToString());
            Cmd.CommandText = sql20;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                成品N80接箍期初库存 = Reader.GetDouble(0);
                成品N80接箍本期入库 = Reader.GetDouble(1);
                成品N80接箍本期出库 = Reader.GetDouble(2);
                成品N80接箍期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();

            //自产油管接箍
            string sql18 = @"            
            select dbo.自产接箍期初库存(@年度,@月份,1) as 期初库存,dbo.自产接箍本期收入(@年度,@月份,1) as 本期收入,dbo.自产接箍本期发出(@年度,@月份,1) as 本期发出,dbo.自产接箍期末库存(@年度,@月份,1)";
            sql18 = sql18.Replace("@年度", year.ToString());
            sql18 = sql18.Replace("@月份", month.ToString());
            Cmd.CommandText = sql18;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                自产油管接箍期初库存 = Reader.GetInt32(0);
                自产油管接箍本期入库 = Reader.GetInt32(1);
                自产油管接箍本期出库 = Reader.GetInt32(2);
                自产油管接箍期末库存 = Reader.GetInt32(3);
            }
            Reader.Close();
            //自产套管接箍
            string sql19 = @"            
            select dbo.自产接箍期初库存(@年度,@月份,2) as 期初库存,dbo.自产接箍本期收入(@年度,@月份,2) as 本期收入,dbo.自产接箍本期发出(@年度,@月份,2) as 本期发出,dbo.自产接箍期末库存(@年度,@月份,2)";
            sql19= sql19.Replace("@年度", year.ToString());
            sql19 = sql19.Replace("@月份", month.ToString());
            Cmd.CommandText = sql19;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                自产套管接箍期初库存 = Reader.GetInt32(0);
                自产套管接箍本期入库 = Reader.GetInt32(1);
                自产套管接箍本期出库 = Reader.GetInt32(2);
                自产套管接箍期末库存 = Reader.GetInt32(3);
            }
            Reader.Close();
            //聚乙烯
            string sql5 = @"            
            select dbo.材料期初库存(@年度,@月份,0301000007) as 期初库存,dbo.材料本期收入(@年度,@月份,0301000007) as 本期收入,dbo.材料本期发出(@年度,@月份,0301000007) as 本期发出,dbo.材料期末库存(@年度,@月份,0301000007) as 期末库存 ";
            sql5 = sql5.Replace("@年度", year.ToString());           
            sql5= sql5.Replace("@月份", month.ToString());
            Cmd.CommandText = sql5;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                聚乙烯期初库存 = Reader.GetDouble(0);
                聚乙烯本期入库 = Reader.GetDouble(1);
                聚乙烯本期出库 = Reader.GetDouble(2);
                聚乙烯期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //钢带
            string sql6 = @"            
            select dbo.材料期初库存(@年度,@月份,0401000002) as 期初库存,dbo.材料本期收入(@年度,@月份,0401000002) as 本期收入,dbo.材料本期发出(@年度,@月份,0401000002) as 本期发出,dbo.材料期末库存(@年度,@月份,0401000002) as 期末库存";
            sql6 = sql6.Replace("@年度", year.ToString());        
            sql6 = sql6.Replace("@月份", month.ToString());
            Cmd.CommandText = sql6;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                钢带期初库存 = Reader.GetDouble(0);
                钢带本期入库 = Reader.GetDouble(1);
                钢带本期出库 = Reader.GetDouble(2);
                钢带期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //套管螺纹保护环
            string sql7 = @"            
            select dbo.材料期初库存(@年度,@月份,0700000002) as 期初库存,dbo.材料本期收入(@年度,@月份,0700000002) as 本期收入,dbo.材料本期发出(@年度,@月份,0700000002) as 本期发出,dbo.材料期末库存(@年度,@月份,0700000002) as 期末库存";
            sql7 = sql7.Replace("@年度", year.ToString());       
            sql7 = sql7.Replace("@月份", month.ToString());
            Cmd.CommandText = sql7;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管螺纹保护环期初库存 = Reader.GetDouble(0);
                套管螺纹保护环本期入库 = Reader.GetDouble(1);
                套管螺纹保护环本期出库 = Reader.GetDouble(2);
                套管螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();           
            //油管螺纹保护环
            string sql8 = @"            
            select dbo.材料期初库存(@年度,@月份,0700000001) as 期初库存,dbo.材料本期收入(@年度,@月份,0700000001) as 本期收入,dbo.材料本期发出(@年度,@月份,0700000001) as 本期发出,dbo.材料期末库存(@年度,@月份,0700000001) as 期末库存 from 材料";
            sql8 = sql8.Replace("@年度", year.ToString());        
            sql8 = sql8.Replace("@月份", month.ToString());
            Cmd.CommandText = sql8;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管螺纹保护环期初库存 = Reader.GetDouble(0);
                油管螺纹保护环本期入库 = Reader.GetDouble(1);
                油管螺纹保护环本期出库 = Reader.GetDouble(2);
                油管螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //自产螺纹保护环
            string sql17 = @"            
            select dbo.自产保护器期初库存(@年度,@月份,2) as 期初库存,dbo.自产保护器本期收入(@年度,@月份,2) as 本期收入,dbo.自产保护器本期发出(@年度,@月份,2) as 本期发出,dbo.自产保护器期末库存(@年度,@月份,2)";
            sql17 = sql17.Replace("@年度", year.ToString());
            sql17 = sql17.Replace("@月份", month.ToString());
            Cmd.CommandText = sql17;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                自产螺纹保护环期初库存 = Reader.GetDouble(0);
                自产螺纹保护环本期入库 = Reader.GetDouble(1);
                自产螺纹保护环本期出库 = Reader.GetDouble(2);
                自产螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();

            //稀释剂
            string sql9 = @"            
            select (dbo.材料期初库存(@年度,@月份,0301000003)+dbo.材料期初库存(@年度,@月份,0301000004)) as 期初库存,(dbo.材料本期收入(@年度,@月份,0301000003)+dbo.材料本期收入(@年度,@月份,0301000004)) as 本期收入,(dbo.材料本期发出(@年度,@月份,0301000003)+dbo.材料本期发出(@年度,@月份,0301000004)) as 本期发出,(dbo.材料期末库存(@年度,@月份,0301000003)+dbo.材料期末库存(@年度,@月份,0301000004)) as 期末库存";
            sql9 = sql9.Replace("@年度", year.ToString());       
            sql9 = sql9.Replace("@月份", month.ToString());
            Cmd.CommandText = sql9;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                稀释剂期初库存 = Reader.GetDouble(0);
                稀释剂本期入库 = Reader.GetDouble(1);
                稀释剂本期出库 = Reader.GetDouble(2);
                稀释剂期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //油漆
            string sql10 = @"            
            select (dbo.材料期初库存(@年度,@月份,0301000001)+dbo.材料期初库存(@年度,@月份,0301000002)) as 期初库存,(dbo.材料本期收入(@年度,@月份,0301000001)+dbo.材料本期收入(@年度,@月份,0301000002)) as 本期收入,(dbo.材料本期发出(@年度,@月份,0301000001)+dbo.材料本期发出(@年度,@月份,0301000002)) as 本期发出,(dbo.材料期末库存(@年度,@月份,0301000001)+dbo.材料期末库存(@年度,@月份,0301000002)) as 期末库存";
            sql10 = sql10.Replace("@年度", year.ToString());    
            sql10 = sql10.Replace("@月份", month.ToString());
            Cmd.CommandText = sql10;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油漆期初库存 = Reader.GetDouble(0);
                油漆本期入库 = Reader.GetDouble(1);
                油漆本期出库 = Reader.GetDouble(2);
                油漆期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //打包带
            string sql11 = @"            
            select dbo.材料期初库存(@年度,@月份,0401000001) as 期初库存,dbo.材料本期收入(@年度,@月份,0401000001) as 本期收入,dbo.材料本期发出(@年度,@月份,0401000001) as 本期发出,dbo.材料期末库存(@年度,@月份,0401000001) as 期末库存";
            sql11 = sql11.Replace("@年度", year.ToString());        
            sql11 = sql11.Replace("@月份", month.ToString());
            Cmd.CommandText = sql11;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                打包带期初库存 = Reader.GetDouble(0);
                打包带本期入库 = Reader.GetDouble(1);
                打包带本期出库 = Reader.GetDouble(2);
                打包带期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //螺纹脂
            string sql12 = @"            
            select dbo.材料期初库存(@年度,@月份,0302000001) as 期初库存,dbo.材料本期收入(@年度,@月份,0302000001) as 本期收入,dbo.材料本期发出(@年度,@月份,0302000001) as 本期发出,dbo.材料期末库存(@年度,@月份,0302000001) as 期末库存";
            sql12 = sql12.Replace("@年度", year.ToString());   
            sql12 = sql12.Replace("@月份", month.ToString());
            Cmd.CommandText = sql12;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                螺纹脂期初库存 = Reader.GetDouble(0);
                螺纹脂本期入库 = Reader.GetDouble(1);
                螺纹脂本期出库 = Reader.GetDouble(2);
                螺纹脂期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //色环漆
            string sql13 = @"            
            select dbo.材料期初库存(@年度,@月份,0301000011) as 期初库存,dbo.材料本期收入(@年度,@月份,0301000011) as 本期收入,dbo.材料本期发出(@年度,@月份,0301000011) as 本期发出,dbo.材料期末库存(@年度,@月份,0301000011) as 期末库存";
            sql13 = sql13.Replace("@年度", year.ToString());        
            sql13 = sql13.Replace("@月份", month.ToString());
            Cmd.CommandText = sql13;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                色环漆期初库存 = Reader.GetDouble(0);
                色环漆本期入库 = Reader.GetDouble(1);
                色环漆本期出库 = Reader.GetDouble(2);
                色环漆期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //接箍漆
            string sql14 = @"            
            select dbo.材料期初库存(@年度,@月份,0300000007) as 期初库存,dbo.材料本期收入(@年度,@月份,0300000007) as 本期收入,dbo.材料本期发出(@年度,@月份,0300000007) as 本期发出,dbo.材料期末库存(@年度,@月份,0300000007) as 期末库存";
            sql14 = sql14.Replace("@年度", year.ToString());          
            sql14 = sql14.Replace("@月份", month.ToString());
            Cmd.CommandText = sql14;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                接箍漆期初库存 = Reader.GetDouble(0);
                接箍漆本期入库 = Reader.GetDouble(1);
                接箍漆本期出库 = Reader.GetDouble(2);
                接箍漆期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //白墨
            string sql15 = @"            
            select dbo.材料期初库存(@年度,@月份,0303000007) as 期初库存,dbo.材料本期收入(@年度,@月份,0303000007) as 本期收入,dbo.材料本期发出(@年度,@月份,0303000007) as 本期发出,dbo.材料期末库存(@年度,@月份,0303000007) as 期末库存";
            sql15 = sql15.Replace("@年度", year.ToString());  
            sql15 = sql15.Replace("@月份", month.ToString());
            Cmd.CommandText = sql15;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                白墨期初库存 = Reader.GetDouble(0);
                白墨本期入库 = Reader.GetDouble(1);
                白墨本期出库 = Reader.GetDouble(2);
                白墨期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //草支垫
            string sql16 = @"            
            select dbo.材料期初库存(@年度,@月份,0700000003) as 期初库存,dbo.材料本期收入(@年度,@月份,0700000003) as 本期收入,dbo.材料本期发出(@年度,@月份,0700000003) as 本期发出,dbo.材料期末库存(@年度,@月份,0700000003) as 期末库存";
            sql16 = sql16.Replace("@年度", year.ToString());            
            sql16 = sql16.Replace("@月份", month.ToString());
            Cmd.CommandText = sql16;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                草支垫期初库存 = Reader.GetDouble(0);
                草支垫本期入库 = Reader.GetDouble(1);
                草支垫本期出库 = Reader.GetDouble(2);
                草支垫期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
        protected void WriteToFile()
        {
            DateTime d;

            d = DateTime.Today;

            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/JueCe/模板/月原辅料收发平衡表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "陕西延长石油材料公司月原辅料收发平衡表";
            hssfworkbook.SummaryInformation = si;
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("原辅料收发平衡表");
            Button_Query_Click(null, null);
            sheet1.GetRow(0).GetCell(0).SetCellValue(this .Year0.Text+"年"+this.Month0.Text + "月份原辅材料收发平衡表");
            sheet1.GetRow(1).GetCell(7).SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"));
            sheet1.GetRow(3).GetCell(3).SetCellValue(套管光管期初库存);
            sheet1.GetRow(3).GetCell(4).SetCellValue(套管光管本期入库);
            sheet1.GetRow(3).GetCell(5).SetCellValue(套管光管本期出库);
            sheet1.GetRow(3).GetCell(6).SetCellValue(套管光管期末库存);
            sheet1.GetRow(3).GetCell(7).SetCellValue(套管光管未入库);

            sheet1.GetRow(4).GetCell(3).SetCellValue(套管N80光管期初库存);
            sheet1.GetRow(4).GetCell(4).SetCellValue(套管N80光管本期入库);
            sheet1.GetRow(4).GetCell(5).SetCellValue(套管N80光管本期出库);
            sheet1.GetRow(4).GetCell(6).SetCellValue(套管N80光管期末库存);
            sheet1.GetRow(4).GetCell(7).SetCellValue(套管N80光管未入库);

            sheet1.GetRow(5).GetCell(3).SetCellValue(油管光管期初库存);
            sheet1.GetRow(5).GetCell(4).SetCellValue(油管光管本期入库);
            sheet1.GetRow(5).GetCell(5).SetCellValue(油管光管本期出库);
            sheet1.GetRow(5).GetCell(6).SetCellValue(油管光管期末库存);
            sheet1.GetRow(5).GetCell(7).SetCellValue(油管光管未入库);

            sheet1.GetRow(6).GetCell(3).SetCellValue(油管接箍光管期初库存);
            sheet1.GetRow(6).GetCell(4).SetCellValue(油管接箍光管本期入库);
            sheet1.GetRow(6).GetCell(5).SetCellValue(油管接箍光管本期出库);
            sheet1.GetRow(6).GetCell(6).SetCellValue(油管接箍光管期末库存);
            sheet1.GetRow(6).GetCell(7).SetCellValue(油管接箍光管未入库);

            sheet1.GetRow(7).GetCell(3).SetCellValue(套管接箍光管期初库存);
            sheet1.GetRow(7).GetCell(4).SetCellValue(套管接箍光管本期入库);
            sheet1.GetRow(7).GetCell(5).SetCellValue(套管接箍光管本期出库);
            sheet1.GetRow(7).GetCell(6).SetCellValue(套管接箍光管期末库存);
            sheet1.GetRow(7).GetCell(7).SetCellValue(套管接箍光管未入库);

            sheet1.GetRow(8).GetCell(3).SetCellValue(自产油管接箍期初库存);
            sheet1.GetRow(8).GetCell(4).SetCellValue(自产油管接箍本期入库);
            sheet1.GetRow(8).GetCell(5).SetCellValue(自产油管接箍本期出库);
            sheet1.GetRow(8).GetCell(6).SetCellValue(自产油管接箍期末库存);

            sheet1.GetRow(9).GetCell(3).SetCellValue(自产套管接箍期初库存);
            sheet1.GetRow(9).GetCell(4).SetCellValue(自产套管接箍本期入库);
            sheet1.GetRow(9).GetCell(5).SetCellValue(自产套管接箍本期出库);
            sheet1.GetRow(9).GetCell(6).SetCellValue(自产套管接箍期末库存);

            sheet1.GetRow(10).GetCell(3).SetCellValue(聚乙烯期初库存);
            sheet1.GetRow(10).GetCell(4).SetCellValue(聚乙烯本期入库);
            sheet1.GetRow(10).GetCell(5).SetCellValue(聚乙烯本期出库);
            sheet1.GetRow(10).GetCell(6).SetCellValue(聚乙烯期末库存);

            sheet1.GetRow(11).GetCell(3).SetCellValue(钢带期初库存);
            sheet1.GetRow(11).GetCell(4).SetCellValue(钢带本期入库);
            sheet1.GetRow(11).GetCell(5).SetCellValue(钢带本期出库);
            sheet1.GetRow(11).GetCell(6).SetCellValue(钢带期末库存);

            sheet1.GetRow(12).GetCell(3).SetCellValue(成品接箍期初库存);
            sheet1.GetRow(12).GetCell(4).SetCellValue(成品接箍本期入库);
            sheet1.GetRow(12).GetCell(5).SetCellValue(成品接箍本期出库);
            sheet1.GetRow(12).GetCell(6).SetCellValue(成品接箍期末库存);
            sheet1.GetRow(12).GetCell(7).SetCellValue(成品接箍未入库);

            sheet1.GetRow(13).GetCell(3).SetCellValue(成品N80接箍期初库存);
            sheet1.GetRow(13).GetCell(4).SetCellValue(成品N80接箍本期入库);
            sheet1.GetRow(13).GetCell(5).SetCellValue(成品N80接箍本期出库);
            sheet1.GetRow(13).GetCell(6).SetCellValue(成品N80接箍期末库存);
            sheet1.GetRow(13).GetCell(7).SetCellValue(成品N80接箍未入库);

            sheet1.GetRow(14).GetCell(3).SetCellValue(套管螺纹保护环期初库存);
            sheet1.GetRow(14).GetCell(4).SetCellValue(套管螺纹保护环本期入库);
            sheet1.GetRow(14).GetCell(5).SetCellValue(套管螺纹保护环本期出库);
            sheet1.GetRow(14).GetCell(6).SetCellValue(套管螺纹保护环期末库存);

            sheet1.GetRow(15).GetCell(3).SetCellValue(油管螺纹保护环期初库存);
            sheet1.GetRow(15).GetCell(4).SetCellValue(油管螺纹保护环本期入库);
            sheet1.GetRow(15).GetCell(5).SetCellValue(油管螺纹保护环本期出库);
            sheet1.GetRow(15).GetCell(6).SetCellValue(油管螺纹保护环期末库存);

            sheet1.GetRow(16).GetCell(3).SetCellValue(自产螺纹保护环期初库存);
            sheet1.GetRow(16).GetCell(4).SetCellValue(自产螺纹保护环本期入库);
            sheet1.GetRow(16).GetCell(5).SetCellValue(自产螺纹保护环本期出库);
            sheet1.GetRow(16).GetCell(6).SetCellValue(自产螺纹保护环期末库存);

            sheet1.GetRow(17).GetCell(3).SetCellValue(稀释剂期初库存);
            sheet1.GetRow(17).GetCell(4).SetCellValue(稀释剂本期入库);
            sheet1.GetRow(17).GetCell(5).SetCellValue(稀释剂本期出库);
            sheet1.GetRow(17).GetCell(6).SetCellValue(稀释剂期末库存);

            sheet1.GetRow(18).GetCell(3).SetCellValue(油漆期初库存);
            sheet1.GetRow(18).GetCell(4).SetCellValue(油漆本期入库);
            sheet1.GetRow(18).GetCell(5).SetCellValue(油漆本期出库);
            sheet1.GetRow(18).GetCell(6).SetCellValue(油漆期末库存);

            sheet1.GetRow(19).GetCell(3).SetCellValue("");
            sheet1.GetRow(19).GetCell(4).SetCellValue("");
            sheet1.GetRow(19).GetCell(5).SetCellValue("");
            sheet1.GetRow(19).GetCell(6).SetCellValue("");

            sheet1.GetRow(20).GetCell(3).SetCellValue(打包带期初库存);
            sheet1.GetRow(20).GetCell(4).SetCellValue(打包带本期入库);
            sheet1.GetRow(20).GetCell(5).SetCellValue(打包带本期出库);
            sheet1.GetRow(20).GetCell(6).SetCellValue(打包带期末库存);

            sheet1.GetRow(21).GetCell(3).SetCellValue(螺纹脂期初库存);
            sheet1.GetRow(21).GetCell(4).SetCellValue(螺纹脂本期入库);
            sheet1.GetRow(21).GetCell(5).SetCellValue(螺纹脂本期出库);
            sheet1.GetRow(21).GetCell(6).SetCellValue(螺纹脂期末库存);

            sheet1.GetRow(22).GetCell(3).SetCellValue(色环漆期初库存);
            sheet1.GetRow(22).GetCell(4).SetCellValue(色环漆本期入库);
            sheet1.GetRow(22).GetCell(5).SetCellValue(色环漆本期出库);
            sheet1.GetRow(22).GetCell(6).SetCellValue(色环漆期末库存);

            sheet1.GetRow(23).GetCell(3).SetCellValue(接箍漆期初库存);
            sheet1.GetRow(23).GetCell(4).SetCellValue(接箍漆本期入库);
            sheet1.GetRow(23).GetCell(5).SetCellValue(接箍漆本期出库);
            sheet1.GetRow(23).GetCell(6).SetCellValue(接箍漆期末库存);

            sheet1.GetRow(24).GetCell(3).SetCellValue(白墨期初库存);
            sheet1.GetRow(24).GetCell(4).SetCellValue(白墨本期入库);
            sheet1.GetRow(24).GetCell(5).SetCellValue(白墨本期出库);
            sheet1.GetRow(24).GetCell(6).SetCellValue(白墨期末库存);

            sheet1.GetRow(25).GetCell(3).SetCellValue(草支垫期初库存);
            sheet1.GetRow(25).GetCell(4).SetCellValue(草支垫本期入库);
            sheet1.GetRow(25).GetCell(5).SetCellValue(草支垫本期出库);
            sheet1.GetRow(25).GetCell(6).SetCellValue(草支垫期末库存);

            WriteToFile();
            Response.End();
        }

       
    }
}
